



 /*  SELECT s.produit,s.magasin,ac.adrcase,cq.qtecase,c.depot,c.magasin,c.allee,c.cases
   from uv_pstocks s,uv_pstocks_adrcase ac,uv_pstocks_qtecase cq, uv_pcases c

   WHERE ac.id=s.id
     AND cq.id=ac.id AND cq.RANGMULTI2=ac.RANGMULTI2
     AND c.id=ac.adrcase
   ORDER BY s.PRODUIT

    erp_mli2.sto_stock
    SELECT * from  erp_mli2.sto_case
    WHERE depot_cod || MAGASIN_COD || ALLEE_COD || CASE_COD ='019012067'  */

--POUR CONTROLE

                              /*SELECT * FROM uv_pcases    */
define erp='erp_mli2.';
define outils='outils_mli2.';

--CREATE TABLE se_sto_stock AS (SELECT * FROM &erp.sto_stock WHERE 1=2) ;

 /*retrouver les dat de rgt et pick*/
 DROP TABLE temp_se_sto_stock_rgtpickdat ;
 CREATE table temp_se_sto_stock_rgtpickdat
 AS (
  SELECT c.id,a.cleprod,To_Date('31/12/1967','dd/mm/yyyy')+d.datmajart dat_rgt,p.qtepick,r.qtergt,
      To_Date('31/12/1967','dd/mm/yyyy')+datmajcase dat_pick
  FROM uv_pcases c, uv_pcases_cleprod a, uv_pcases_datmajart  d, uv_pcases_qtepick p, uv_pcases_qtergt r
  WHERE a.id=c.id
    AND d.id=a.id AND d.rangmulti2=a.rangmulti2
    AND p.id=d.id AND p.rangmulti2=d.rangmulti2 AND p.rangmulti2=d.rangmulti2
    AND r.id=d.id AND r.rangmulti2=d.rangmulti2 AND r.rangmulti3=d.rangmulti3
    --AND (p.qtepick!=0 OR r.qtergt!=0)
 -- ORDER BY cleprod
 ) ;

DROP TABLE temp_se_sto_stock;
CREATE TABLE temp_se_sto_stock AS
  (SELECT c.depot depot_cod,
         v.combsto_id combsto_id,
         Decode(Decode(c.depot,'01','O','05','O','06','O','07','O','N'),
                  'O',(SELECT enreg_id from  erp_mli2.sto_case cc WHERE cc.depot_cod=c.depot AND cc.magasin_cod=c.magasin AND cc.allee_cod=c.allee AND cc.case_cod=c.cases)
                  ,'N',(SELECT CASE_NEUTRE_ID FROM &erp.sto_depot dd WHERE dd.depot_cod=c.depot) )case_id,
         0 lotint_cod,
         0 serie_cod,
         0 enreg_id,
         cq.qtecase actuel_qt,
         Nvl((SELECT dat_rgt FROM temp_se_sto_stock_rgtpickdat d WHERE d.id=c.id AND d.CLEPROD=p.id),To_Date('01/01/1970','dd/mm/yyyy')) rgt_dat,
         Nvl((SELECT dat_pick FROM temp_se_sto_stock_rgtpickdat d WHERE d.id=c.id AND d.CLEPROD=p.id),To_Date('01/01/1970','dd/mm/yyyy')) pck_dat
   FROM uv_pstocks s,uv_pstocks_adrcase ac,uv_pstocks_qtecase cq, uv_pcases c,uv_pproduits p,&erp.vrt_combsto v

   WHERE ac.id=s.id
     AND cq.id=ac.id
     AND cq.RANGMULTI2=ac.RANGMULTI2
     AND c.id=ac.adrcase
     AND p.id=s.produit
     AND v.combsto_id=p.numspecif

  )  ;


DELETE FROM se_sto_stock ;

 INSERT INTO se_sto_stock
 SELECT DEPOT_COD, COMBSTO_ID, CASE_ID, LOTINT_COD, SERIE_COD, ENREG_ID, Sum(ACTUEL_QT) ACTUEL_QT, Min(RGT_DAT) RGT_DAT, Max(PCK_DAT) PCK_DAT
 from temp_se_sto_stock
GROUP BY DEPOT_COD, COMBSTO_ID, CASE_ID, LOTINT_COD, SERIE_COD, ENREG_ID ;

 UPDATE se_sto_stock tf SET enreg_id=Nvl((select enreg_id FROM &erp.sto_stock f WHERE f.depot_cod=tf.depot_cod and f.combsto_id=tf.combsto_id AND f.CASE_ID=tf.case_id AND f.LOTINT_COD=tf.lotint_cod AND f.SERIE_COD=tf.serie_cod),0) ;
 UPDATE se_sto_stock tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0 ;

 DELETE FROM &erp.sto_stock ;

 CALL uvse_synchroverserp('enreg_id','STO_STOCK','O') ;

 commit;






